package org.zh.sh.util.jdbc;

import org.zh.sh.util.NonNull;

import java.sql.*;
import java.util.*;
import java.util.logging.Level;

import static org.zh.sh.config.GlobalStatic.*;


public class ZHOpenJdbcUtil {
   private ZHOpenJdbcUtil(){}


    private static Connection getConn() {
        return localConn.get();
    }


    public static int executeUpdate(@NonNull String sql, List<Object> parameters) {
        int updateCount = 0;
        try {
            Connection conn = getConn();
            PreparedStatement stmt = conn.prepareStatement(sql);
            for (int i = 0, size = parameters.size(); i < size; ++i) {
                Object param = parameters.get(i);
                stmt.setObject(i + 1, param);
            }
            updateCount = stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            log.log(Level.SEVERE, "ZHOpenJdbcUtil  jdbc 错误", e);
        }
        return updateCount;
    }

    public static int executeUpdate(@NonNull String sql, Object... parameters) {
        return executeUpdate(sql, Arrays.asList(parameters));
    }

    public static void execute(@NonNull String sql) {
        execute(sql, Collections.emptyList());
    }

    public static void execute(@NonNull String sql, List<Object> parameters) {

        try {
            Connection conn = getConn();
            PreparedStatement stmt = conn.prepareStatement(sql);
            for (int i = 0, size = parameters.size(); i < size; ++i) {
                Object param = parameters.get(i);
                stmt.setObject(i + 1, param);
            }
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            log.log(Level.SEVERE, "ZHOpenJdbcUtil  jdbc 错误", e);
        }
    }

    public static void execute(@NonNull String sql, Object... parameters) {
        execute(sql, Arrays.asList(parameters));
    }

    public static List<Map<String, Object>> executeQuery(@NonNull String sql, List<Object> parameters) {
        List<Map<String, Object>> rows = null;
        try {
            Connection conn = getConn();
            PreparedStatement stmt = conn.prepareStatement(sql);
            for (int i = 0, size = parameters.size(); i < size; ++i) {
                Object param = parameters.get(i);
                stmt.setObject(i + 1, param);
            }
            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData rsMeta = rs.getMetaData();
            rows = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap<>();
                for (int i = 0, size = rsMeta.getColumnCount(); i < size; ++i) {
                    String columName = rsMeta.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);
                    row.put(columName, value);
                }
                rows.add(row);
            }
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            log.log(Level.SEVERE, "ZHOpenJdbcUtil  jdbc 错误", e);
        }
        return rows;
    }

    public static List<Map<String, Object>> executeQuery(@NonNull String sql, Object... parameters) {
        return executeQuery(sql, Arrays.asList(parameters));
    }

    public static Map<String, Object> executeQueryOne(@NonNull String sql, Object... parameters) {
        List<Map<String, Object>> maps = executeQuery(sql, Arrays.asList(parameters));
        if (maps.isEmpty()) {
            log.log(Level.SEVERE, "查迅结果为空 抛出异常", new RuntimeException());
        }
        if (maps.size() > 1) {
            log.log(Level.SEVERE, "查迅多个结果 抛出异常", new RuntimeException());
        }
        return maps.getFirst();
    }

    public static void insertVoid(@NonNull String tableName, Map<String, Object> data) {
        String sql = makeInsertToTableSql(tableName, data.keySet());
        List<Object> parameters = new ArrayList<>(data.values());
        try {
            Connection conn = getConn();
            PreparedStatement stmt = conn.prepareStatement(sql);
            for (int i = 0, size = parameters.size(); i < size; ++i) {
                Object param = parameters.get(i);
                stmt.setObject(i + 1, param);
            }
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            log.log(Level.SEVERE, "ZHOpenJdbcUtil  jdbc 错误", e);
        }
    }

    public static Map<String, Object> insertPri(@NonNull String tableName, Map<String, Object> data) {
        String sql = makeInsertToTableSql(tableName, data.keySet());
        try {
            Connection conn = getConn();
            PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            List<Object> parameters = new ArrayList<>(data.values());
            for (int i = 0, size = parameters.size(); i < size; ++i) {
                Object param = parameters.get(i);
                ps.setObject(i + 1, param);
            }
            int count = ps.executeUpdate();
            String priKeys = getPriKey(tableName);
            if (count > 0 && priKeys != null) {
                ResultSet gk = ps.getGeneratedKeys();
                if (gk.next()) {
                        data.put(priKeys, gk.getObject( 1));
                }
                gk.close();
            }
            ps.close();
        } catch (SQLException e) {
            log.log(Level.SEVERE, "ZHOpenJdbcUtil  jdbc 错误", e);
        }
        return data;
    }

    private static String makeInsertToTableSql(String tableName, Collection<String> names) {
        StringBuilder sql = new StringBuilder()
                .append("insert into ")
                .append(tableName)
                .append("(");

        int nameCount = 0;
        for (String name : names) {
            if (nameCount > 0) {
                sql.append(",");
            }
            sql.append(name);
            nameCount++;
        }
        sql.append(") values (");
        for (int i = 0; i < nameCount; ++i) {
            if (i != 0) {
                sql.append(",");
            }
            sql.append("?");
        }
        sql.append(")");

        return sql.toString();
    }


}